﻿using System;
using System.Collections.Generic;
using System.Text;
using AD.Model;
using System.Data;
using System.Data.SqlClient;

namespace AD.DAL
{
    public class KeyWordsDAL : DALBase
    {
        public static List<KeyWordsMod> Query(string keywords, string engine)
        {
            List<KeyWordsMod> accountFlows = new List<KeyWordsMod>();
            try
            {
                string sql = "SELECT * FROM tab_keywords_info join tab_users_info on tab_keywords_info.UserId = tab_users_info.AutoID" +
                            " WHERE BalancePoint>0 AND Status=1 AND IsUse=1 AND IsValid=1 AND IsFrozen=0 AND SearchEngineType={0} AND KeyWords LIKE '%{1}%'";
                sql = string.Format(sql, engine, keywords);
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    KeyWordsMod user = SetModel(row);
                    if (user != null) accountFlows.Add(user);
                }
            }
            catch { }
            return accountFlows;
        }

        public static List<KeyWordsMod> QueryByPage(string filter, int pageSize, int index, out int maxCount)
        {
            maxCount = 0;
            try
            {
                maxCount = Count(filter);
                string sql = "select top {0} * from tab_keywords_info where (AutoID NOT IN (select top {1} AutoID from tab_keywords_info order by AutoID)) order by AutoID";
                sql = string.Format(sql, pageSize, index * pageSize);
                if (!string.IsNullOrEmpty(filter))
                {
                    sql = "select top {0} * from tab_keywords_info where {2} and (AutoID NOT IN (select top {1} AutoID from tab_keywords_info where {2} order by AutoID)) order by AutoID";
                    sql = string.Format(sql, pageSize, index * pageSize, filter);
                }
                DataTable dt = ExecuteDataTable(sql);
                if (dt == null || dt.Rows.Count <= 0) return null;
                List<KeyWordsMod> accountFlows = new List<KeyWordsMod>();
                foreach (DataRow row in dt.Rows)
                {
                    KeyWordsMod user = SetModel(row);
                    user.User = UserDAL.QueryFrist(string.Format(" AutoID='{0}'", user.UserId));
                    accountFlows.Add(user);
                }
                return accountFlows;
            }
            catch { return null; }
        }

        public static int Count(string filter)
        {
            return QueryFilterToTable(filter).Rows.Count;
        }

        public static int Insert(KeyWordsMod keyword)
        {
            try
            {
                if (CheckKeyWords(keyword) != null) return 0;
                string sql = "insert into tab_keywords_info(SearchEngineType,IsUse,UserId,KeyWords,AdTitle,AdDescribe_First,AdDescribe_Second,AdUrl,AdUrl_Title,Commit_Date,Status) values(@SearchEngineType,@IsUse,@UserId,@KeyWords,@AdTitle,@AdDescribe_First,@AdDescribe_Second,@AdUrl,@AdUrl_Title,@Commit_Date,@Status)";
                SqlParameter[] sqlParams = GetSqlParameter(keyword);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }
        public static int UpdateUse(bool use, string id)
        {
            try
            {
                string sql = "update tab_keywords_info set IsUse=" + (use == true ? 1 : 0) + ", UseUpdateDate=getdate() where AutoID=" + id;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }
        public static int UpdateInfo(KeyWordsMod keyword)
        {
            try
            {
                string sql = "update tab_keywords_info set KeyWords=@KeyWords,SearchEngineType=@SearchEngineType, IsUse=@IsUse,Status=@Status,AdTitle=@AdTitle,AdDescribe_First=@AdDescribe_First,AdDescribe_Second=@AdDescribe_Second,AdUrl=@AdUrl,AdUrl_Title=@AdUrl_Title where AutoID=@AutoID";
                SqlParameter[] sqlParams = GetSqlParameter(keyword);
                return ExecuteNonQuery(sql, sqlParams);
            }
            catch
            {
                return 0;
            }
        }

        public static int Delete(KeyWordsMod keyword)
        {
            return Delete(keyword.AutoID);
        }

        public static int Delete(int autoId)
        {
            try
            {
                string sql = "delete from tab_keywords_info where AutoID=" + autoId;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateAudit(KeyWordsMod keyword)
        {
            try
            {
                return UpdateAudit(keyword.Status, keyword.AutoID);
            }
            catch
            {
                return 0;
            }
        }

        public static int UpdateAudit(KeyWordsStatus status, int id)
        {
            try
            {
                string sql = "update tab_keywords_info set Status=" + (int)status + " where AutoID=" + id;
                return ExecuteNonQuery(sql, null);
            }
            catch
            {
                return 0;
            }
        }

        public static KeyWordsMod CheckKeyWords(KeyWordsMod keyWords)
        {
            try
            {
                string filter = " UserId=" + keyWords.UserId + " and KeyWords='" + keyWords.KeyWords + "'";
                DataTable dt = QueryFilterToTable(filter);
                if (dt == null || dt.Rows.Count <= 0) return null;
                foreach (DataRow row in dt.Rows)
                {
                    KeyWordsMod tmp = SetModel(row);
                    if (tmp.SearchEngineType == keyWords.SearchEngineType)
                    {
                        return tmp;
                    }
                }
            }
            catch { }
            return null;
        }

         public static IList<KeyWordsMod> QueryFilterToList(string filter)
        {
            DataTable dt = QueryFilterToTable(filter);
            if (dt == null) return null;
            IList<KeyWordsMod> lists = new List<KeyWordsMod>();
            foreach (DataRow row in dt.Rows)
            {
                lists.Add(SetModel(row));
            }
            return lists;
        }

         public static int Sum(string filter)
         {
             try
             {
                 string sql = "Select count(*) from tab_keywords_info ";
                 if (!string.IsNullOrEmpty(filter))
                     sql += " where " + filter;
                 SqlDataReader reader = ExecuteReader(sql);
                 while (reader.Read())
                 {
                     return reader.GetInt32(0);
                 }
             }
             catch { }
             return 0;
         }
        public static DataTable QueryFilterToTable(string filter)
        {
            try
            {
                string sql = "Select * from tab_keywords_info ";
                if (!string.IsNullOrEmpty(filter))
                    sql += " where " + filter;
                return ExecuteDataTable(sql);
            }
            catch
            {
            }
            return null;
        }

        #region 模型

        static KeyWordsMod SetModel(DataRow row)
        {
            KeyWordsMod keywords = new KeyWordsMod();
            keywords.AutoID = int.Parse(row["AutoID"].ToString());

            if (row["AdDescribe_First"] != DBNull.Value)
                keywords.AdDescribe_First = row["AdDescribe_First"].ToString();

            if (row["IsUse"] != DBNull.Value)
                keywords.IsUse = bool.Parse(row["IsUse"].ToString());

            if (row["AdDescribe_Second"] != DBNull.Value)
                keywords.AdDescribe_Second = row["AdDescribe_Second"].ToString();

            if (row["AdTitle"] != DBNull.Value)
                keywords.AdTitle = row["AdTitle"].ToString();

            if (row["AdUrl"] != DBNull.Value)
                keywords.AdUrl = row["AdUrl"].ToString();

            if (row["AdUrl_Title"] != DBNull.Value)
                keywords.AdUrl_Title = row["AdUrl_Title"].ToString();

            if (row["Commit_Date"] != DBNull.Value)
                keywords.Commit_Date = DateTime.Parse(row["Commit_Date"].ToString());

            
            if (row["KeyWords"] != DBNull.Value)
                keywords.KeyWords = row["KeyWords"].ToString();

            if (row["SearchEngineType"] != DBNull.Value)
                keywords.SearchEngineType = (SearchEngineType)int.Parse(row["SearchEngineType"].ToString());
            
            if (row["Status"] != DBNull.Value)
                keywords.Status = (KeyWordsStatus)int.Parse(row["Status"].ToString());

            if (row["UseUpdateDate"] != DBNull.Value)
                keywords.UseUpdateDate = DateTime.Parse(row["UseUpdateDate"].ToString());

            if (row["UserId"] != DBNull.Value)
                keywords.UserId = int.Parse(row["UserId"].ToString());
            return keywords;
        }

        static SqlParameter[] GetSqlParameter(KeyWordsMod keywords)
        {
            List<SqlParameter> param = new List<SqlParameter>();
            SqlParameter AutoID = new SqlParameter("@AutoID", SqlDbType.Int, 4, "AutoID");
            AutoID.Value = keywords.AutoID;
            param.Add(AutoID);

            SqlParameter Commit_Date = new SqlParameter("@Commit_Date", SqlDbType.DateTime, 8, "Commit_Date");
            Commit_Date.Value = keywords.Commit_Date;
            param.Add(Commit_Date);

            SqlParameter IsUse = new SqlParameter("@IsUse", SqlDbType.Bit, 1, "IsUse");
            IsUse.Value = keywords.IsUse;
            param.Add(IsUse);

            SqlParameter AdDescribe_First = new SqlParameter("@AdDescribe_First", SqlDbType.VarChar, 50, "AdDescribe_First");
            AdDescribe_First.Value = keywords.AdDescribe_First;
            param.Add(AdDescribe_First);

            SqlParameter AdDescribe_Second = new SqlParameter("@AdDescribe_Second", SqlDbType.VarChar, 50, "AdDescribe_Second");
            AdDescribe_Second.Value = keywords.AdDescribe_Second;
            param.Add(AdDescribe_Second);

            SqlParameter AdTitle = new SqlParameter("@AdTitle", SqlDbType.VarChar, 50, "AdTitle");
            AdTitle.Value = keywords.AdTitle;
            param.Add(AdTitle);

            SqlParameter AdUrl = new SqlParameter("@AdUrl", SqlDbType.VarChar, 100, "AdUrl");
            AdUrl.Value = keywords.AdUrl;
            param.Add(AdUrl);

            SqlParameter AdUrl_Title = new SqlParameter("@AdUrl_Title", SqlDbType.VarChar, 50, "AdUrl_Title");
            AdUrl_Title.Value = keywords.AdUrl_Title;
            param.Add(AdUrl_Title);

            SqlParameter KeyWords = new SqlParameter("@KeyWords", SqlDbType.VarChar, 50, "KeyWords");
            KeyWords.Value = keywords.KeyWords;
            param.Add(KeyWords);

            SqlParameter Status = new SqlParameter("@Status", SqlDbType.SmallInt, 4, "Status");
            Status.Value = (int)keywords.Status;
            param.Add(Status);

            SqlParameter SearchEngineType = new SqlParameter("@SearchEngineType", SqlDbType.Int, 4, "SearchEngineType");
            SearchEngineType.Value = (int)keywords.SearchEngineType;
            param.Add(SearchEngineType);
            
            SqlParameter UserId = new SqlParameter("@UserId", SqlDbType.Int, 8, "UserId");
            UserId.Value = keywords.UserId;
            param.Add(UserId);

            return param.ToArray();
        }

        #endregion
    }
}